{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from WORLD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.3\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.0     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introduction\n",
    "\n",
    "[Read the notes about this table](https://sqlzoo.net/wiki/Read_the_notes_about_this_table.). Observe the result of running this SQL command to show the name, continent and population of all countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "world <- dbReadTable(con, 'world')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 195 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>continent</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Afghanistan        </td><td>Asia         </td><td>25500100</td></tr>\n",
       "\t<tr><td>Albania            </td><td>Europe       </td><td> 2821977</td></tr>\n",
       "\t<tr><td>Algeria            </td><td>Africa       </td><td>38700000</td></tr>\n",
       "\t<tr><td>Andorra            </td><td>Europe       </td><td>   76098</td></tr>\n",
       "\t<tr><td>Angola             </td><td>Africa       </td><td>19183590</td></tr>\n",
       "\t<tr><td>Antigua and Barbuda</td><td>Caribbean    </td><td>   86295</td></tr>\n",
       "\t<tr><td>Argentina          </td><td>South America</td><td>42669500</td></tr>\n",
       "\t<tr><td>Armenia            </td><td>Eurasia      </td><td> 3017400</td></tr>\n",
       "\t<tr><td>Australia          </td><td>Oceania      </td><td>23545500</td></tr>\n",
       "\t<tr><td>Austria            </td><td>Europe       </td><td> 8504850</td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>United States</td><td>North America</td><td>318320000</td></tr>\n",
       "\t<tr><td>Uruguay      </td><td>South America</td><td>  3286314</td></tr>\n",
       "\t<tr><td>Uzbekistan   </td><td>Asia         </td><td> 30492800</td></tr>\n",
       "\t<tr><td>Vanuatu      </td><td>Oceania      </td><td>   264652</td></tr>\n",
       "\t<tr><td>Vatican City </td><td>Europe       </td><td>      839</td></tr>\n",
       "\t<tr><td>Venezuela    </td><td>South America</td><td> 28946101</td></tr>\n",
       "\t<tr><td>Vietnam      </td><td>Asia         </td><td> 89708900</td></tr>\n",
       "\t<tr><td>Yemen        </td><td>Asia         </td><td> 25235000</td></tr>\n",
       "\t<tr><td>Zambia       </td><td>Africa       </td><td> 15023315</td></tr>\n",
       "\t<tr><td>Zimbabwe     </td><td>Africa       </td><td> 13061239</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 195 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & continent & population\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Afghanistan         & Asia          & 25500100\\\\\n",
       "\t Albania             & Europe        &  2821977\\\\\n",
       "\t Algeria             & Africa        & 38700000\\\\\n",
       "\t Andorra             & Europe        &    76098\\\\\n",
       "\t Angola              & Africa        & 19183590\\\\\n",
       "\t Antigua and Barbuda & Caribbean     &    86295\\\\\n",
       "\t Argentina           & South America & 42669500\\\\\n",
       "\t Armenia             & Eurasia       &  3017400\\\\\n",
       "\t Australia           & Oceania       & 23545500\\\\\n",
       "\t Austria             & Europe        &  8504850\\\\\n",
       "\t ... & ... & ...\\\\\n",
       "\t United States & North America & 318320000\\\\\n",
       "\t Uruguay       & South America &   3286314\\\\\n",
       "\t Uzbekistan    & Asia          &  30492800\\\\\n",
       "\t Vanuatu       & Oceania       &    264652\\\\\n",
       "\t Vatican City  & Europe        &       839\\\\\n",
       "\t Venezuela     & South America &  28946101\\\\\n",
       "\t Vietnam       & Asia          &  89708900\\\\\n",
       "\t Yemen         & Asia          &  25235000\\\\\n",
       "\t Zambia        & Africa        &  15023315\\\\\n",
       "\t Zimbabwe      & Africa        &  13061239\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 195 × 3\n",
       "\n",
       "| name &lt;chr&gt; | continent &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Afghanistan         | Asia          | 25500100 |\n",
       "| Albania             | Europe        |  2821977 |\n",
       "| Algeria             | Africa        | 38700000 |\n",
       "| Andorra             | Europe        |    76098 |\n",
       "| Angola              | Africa        | 19183590 |\n",
       "| Antigua and Barbuda | Caribbean     |    86295 |\n",
       "| Argentina           | South America | 42669500 |\n",
       "| Armenia             | Eurasia       |  3017400 |\n",
       "| Australia           | Oceania       | 23545500 |\n",
       "| Austria             | Europe        |  8504850 |\n",
       "| ... | ... | ... |\n",
       "| United States | North America | 318320000 |\n",
       "| Uruguay       | South America |   3286314 |\n",
       "| Uzbekistan    | Asia          |  30492800 |\n",
       "| Vanuatu       | Oceania       |    264652 |\n",
       "| Vatican City  | Europe        |       839 |\n",
       "| Venezuela     | South America |  28946101 |\n",
       "| Vietnam       | Asia          |  89708900 |\n",
       "| Yemen         | Asia          |  25235000 |\n",
       "| Zambia        | Africa        |  15023315 |\n",
       "| Zimbabwe      | Africa        |  13061239 |\n",
       "\n"
      ],
      "text/plain": [
       "    name                continent     population\n",
       "1   Afghanistan         Asia          25500100  \n",
       "2   Albania             Europe         2821977  \n",
       "3   Algeria             Africa        38700000  \n",
       "4   Andorra             Europe           76098  \n",
       "5   Angola              Africa        19183590  \n",
       "6   Antigua and Barbuda Caribbean        86295  \n",
       "7   Argentina           South America 42669500  \n",
       "8   Armenia             Eurasia        3017400  \n",
       "9   Australia           Oceania       23545500  \n",
       "10  Austria             Europe         8504850  \n",
       "... ...                 ...           ...       \n",
       "186 United States       North America 318320000 \n",
       "187 Uruguay             South America   3286314 \n",
       "188 Uzbekistan          Asia           30492800 \n",
       "189 Vanuatu             Oceania          264652 \n",
       "190 Vatican City        Europe              839 \n",
       "191 Venezuela           South America  28946101 \n",
       "192 Vietnam             Asia           89708900 \n",
       "193 Yemen               Asia           25235000 \n",
       "194 Zambia              Africa         15023315 \n",
       "195 Zimbabwe            Africa         13061239 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    select(name, continent, population)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Large Countries\n",
    "\n",
    "[How to use WHERE to filter records](https://sqlzoo.net/wiki/WHERE_filters). Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 5 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Brazil       </td></tr>\n",
       "\t<tr><td>China        </td></tr>\n",
       "\t<tr><td>India        </td></tr>\n",
       "\t<tr><td>Indonesia    </td></tr>\n",
       "\t<tr><td>United States</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Brazil       \\\\\n",
       "\t China        \\\\\n",
       "\t India        \\\\\n",
       "\t Indonesia    \\\\\n",
       "\t United States\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Brazil        |\n",
       "| China         |\n",
       "| India         |\n",
       "| Indonesia     |\n",
       "| United States |\n",
       "\n"
      ],
      "text/plain": [
       "  name         \n",
       "1 Brazil       \n",
       "2 China        \n",
       "3 India        \n",
       "4 Indonesia    \n",
       "5 United States"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(population>=2e8) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Per capita GDP\n",
    "\n",
    "Give the `name` and the **per capita GDP** for those countries with a `population` of at least 200 million.\n",
    "\n",
    "> _HELP:How to calculate per capita GDP_   \n",
    "> per capita GDP is the GDP divided by the population GDP/population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 5 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>pc_gdp</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Brazil       </td><td>11115.265</td></tr>\n",
       "\t<tr><td>China        </td><td> 6121.711</td></tr>\n",
       "\t<tr><td>India        </td><td> 1504.793</td></tr>\n",
       "\t<tr><td>Indonesia    </td><td> 3482.020</td></tr>\n",
       "\t<tr><td>United States</td><td>51032.295</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & pc\\_gdp\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Brazil        & 11115.265\\\\\n",
       "\t China         &  6121.711\\\\\n",
       "\t India         &  1504.793\\\\\n",
       "\t Indonesia     &  3482.020\\\\\n",
       "\t United States & 51032.295\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 2\n",
       "\n",
       "| name &lt;chr&gt; | pc_gdp &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| Brazil        | 11115.265 |\n",
       "| China         |  6121.711 |\n",
       "| India         |  1504.793 |\n",
       "| Indonesia     |  3482.020 |\n",
       "| United States | 51032.295 |\n",
       "\n"
      ],
      "text/plain": [
       "  name          pc_gdp   \n",
       "1 Brazil        11115.265\n",
       "2 China          6121.711\n",
       "3 India          1504.793\n",
       "4 Indonesia      3482.020\n",
       "5 United States 51032.295"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(population>=2e8) %>% \n",
    "    mutate(pc_gdp=gdp/population) %>%\n",
    "    select(name, pc_gdp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. South America In millions\n",
    "\n",
    "Show the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 13 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>popl</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Argentina                       </td><td> 42.669500</td></tr>\n",
       "\t<tr><td>Bolivia                         </td><td> 10.027254</td></tr>\n",
       "\t<tr><td>Brazil                          </td><td>202.794000</td></tr>\n",
       "\t<tr><td>Chile                           </td><td> 17.773000</td></tr>\n",
       "\t<tr><td>Colombia                        </td><td> 47.662000</td></tr>\n",
       "\t<tr><td>Ecuador                         </td><td> 15.774200</td></tr>\n",
       "\t<tr><td>Guyana                          </td><td>  0.784894</td></tr>\n",
       "\t<tr><td>Paraguay                        </td><td>  6.783374</td></tr>\n",
       "\t<tr><td>Peru                            </td><td> 30.475144</td></tr>\n",
       "\t<tr><td>Saint Vincent and the Grenadines</td><td>  0.109000</td></tr>\n",
       "\t<tr><td>Suriname                        </td><td>  0.534189</td></tr>\n",
       "\t<tr><td>Uruguay                         </td><td>  3.286314</td></tr>\n",
       "\t<tr><td>Venezuela                       </td><td> 28.946101</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 13 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & popl\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Argentina                        &  42.669500\\\\\n",
       "\t Bolivia                          &  10.027254\\\\\n",
       "\t Brazil                           & 202.794000\\\\\n",
       "\t Chile                            &  17.773000\\\\\n",
       "\t Colombia                         &  47.662000\\\\\n",
       "\t Ecuador                          &  15.774200\\\\\n",
       "\t Guyana                           &   0.784894\\\\\n",
       "\t Paraguay                         &   6.783374\\\\\n",
       "\t Peru                             &  30.475144\\\\\n",
       "\t Saint Vincent and the Grenadines &   0.109000\\\\\n",
       "\t Suriname                         &   0.534189\\\\\n",
       "\t Uruguay                          &   3.286314\\\\\n",
       "\t Venezuela                        &  28.946101\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 13 × 2\n",
       "\n",
       "| name &lt;chr&gt; | popl &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| Argentina                        |  42.669500 |\n",
       "| Bolivia                          |  10.027254 |\n",
       "| Brazil                           | 202.794000 |\n",
       "| Chile                            |  17.773000 |\n",
       "| Colombia                         |  47.662000 |\n",
       "| Ecuador                          |  15.774200 |\n",
       "| Guyana                           |   0.784894 |\n",
       "| Paraguay                         |   6.783374 |\n",
       "| Peru                             |  30.475144 |\n",
       "| Saint Vincent and the Grenadines |   0.109000 |\n",
       "| Suriname                         |   0.534189 |\n",
       "| Uruguay                          |   3.286314 |\n",
       "| Venezuela                        |  28.946101 |\n",
       "\n"
      ],
      "text/plain": [
       "   name                             popl      \n",
       "1  Argentina                         42.669500\n",
       "2  Bolivia                           10.027254\n",
       "3  Brazil                           202.794000\n",
       "4  Chile                             17.773000\n",
       "5  Colombia                          47.662000\n",
       "6  Ecuador                           15.774200\n",
       "7  Guyana                             0.784894\n",
       "8  Paraguay                           6.783374\n",
       "9  Peru                              30.475144\n",
       "10 Saint Vincent and the Grenadines   0.109000\n",
       "11 Suriname                           0.534189\n",
       "12 Uruguay                            3.286314\n",
       "13 Venezuela                         28.946101"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(continent=='South America') %>% \n",
    "    mutate(popl=population/1e6) %>%\n",
    "    select(name, popl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. France, Germany, Italy\n",
    "\n",
    "Show the `name` and `population` for France, Germany, Italy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>France </td><td>65906000</td></tr>\n",
       "\t<tr><td>Germany</td><td>80716000</td></tr>\n",
       "\t<tr><td>Italy  </td><td>60782668</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & population\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t France  & 65906000\\\\\n",
       "\t Germany & 80716000\\\\\n",
       "\t Italy   & 60782668\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 2\n",
       "\n",
       "| name &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| France  | 65906000 |\n",
       "| Germany | 80716000 |\n",
       "| Italy   | 60782668 |\n",
       "\n"
      ],
      "text/plain": [
       "  name    population\n",
       "1 France  65906000  \n",
       "2 Germany 80716000  \n",
       "3 Italy   60782668  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(name %in% c('France', 'Germany', 'Italy')) %>% \n",
    "    select(name, population)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. United\n",
    "\n",
    "Show the countries which have a `name` that includes the word 'United'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>United Arab Emirates</td></tr>\n",
       "\t<tr><td>United Kingdom      </td></tr>\n",
       "\t<tr><td>United States       </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t United Arab Emirates\\\\\n",
       "\t United Kingdom      \\\\\n",
       "\t United States       \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| United Arab Emirates |\n",
       "| United Kingdom       |\n",
       "| United States        |\n",
       "\n"
      ],
      "text/plain": [
       "  name                \n",
       "1 United Arab Emirates\n",
       "2 United Kingdom      \n",
       "3 United States       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, 'United')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Two ways to be big\n",
    "\n",
    "Two ways to be big: A country is **big** if it has an area of more than 3 million sq km or it has a population of more than 250 million.\n",
    "\n",
    "**Show the countries that are big by area or big by population. Show name, population and area.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 8 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>population</th><th scope=col>area</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Australia    </td><td>  23545500</td><td> 7692024</td></tr>\n",
       "\t<tr><td>Brazil       </td><td> 202794000</td><td> 8515767</td></tr>\n",
       "\t<tr><td>Canada       </td><td>  35427524</td><td> 9984670</td></tr>\n",
       "\t<tr><td>China        </td><td>1365370000</td><td> 9596961</td></tr>\n",
       "\t<tr><td>India        </td><td>1246160000</td><td> 3166414</td></tr>\n",
       "\t<tr><td>Indonesia    </td><td> 252164800</td><td> 1904569</td></tr>\n",
       "\t<tr><td>Russia       </td><td> 146000000</td><td>17125242</td></tr>\n",
       "\t<tr><td>United States</td><td> 318320000</td><td> 9826675</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 8 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & population & area\\\\\n",
       " <chr> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Australia     &   23545500 &  7692024\\\\\n",
       "\t Brazil        &  202794000 &  8515767\\\\\n",
       "\t Canada        &   35427524 &  9984670\\\\\n",
       "\t China         & 1365370000 &  9596961\\\\\n",
       "\t India         & 1246160000 &  3166414\\\\\n",
       "\t Indonesia     &  252164800 &  1904569\\\\\n",
       "\t Russia        &  146000000 & 17125242\\\\\n",
       "\t United States &  318320000 &  9826675\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 8 × 3\n",
       "\n",
       "| name &lt;chr&gt; | population &lt;dbl&gt; | area &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Australia     |   23545500 |  7692024 |\n",
       "| Brazil        |  202794000 |  8515767 |\n",
       "| Canada        |   35427524 |  9984670 |\n",
       "| China         | 1365370000 |  9596961 |\n",
       "| India         | 1246160000 |  3166414 |\n",
       "| Indonesia     |  252164800 |  1904569 |\n",
       "| Russia        |  146000000 | 17125242 |\n",
       "| United States |  318320000 |  9826675 |\n",
       "\n"
      ],
      "text/plain": [
       "  name          population area    \n",
       "1 Australia       23545500  7692024\n",
       "2 Brazil         202794000  8515767\n",
       "3 Canada          35427524  9984670\n",
       "4 China         1365370000  9596961\n",
       "5 India         1246160000  3166414\n",
       "6 Indonesia      252164800  1904569\n",
       "7 Russia         146000000 17125242\n",
       "8 United States  318320000  9826675"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(population >= 2.5e8 | area >= 3e6) %>% \n",
    "    select(name, population, area)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. One or the other (but not both)\n",
    "\n",
    "**Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.**\n",
    "\n",
    "- Australia has a big area but a small population, it should be **included**.\n",
    "- Indonesia has a big population but a small area, it should be **included**.\n",
    "- China has a big population **and** big area, it should be **excluded**.\n",
    "- United Kingdom has a small population and a small area, it should be **excluded**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 5 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>population</th><th scope=col>area</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Australia</td><td> 23545500</td><td> 7692024</td></tr>\n",
       "\t<tr><td>Brazil   </td><td>202794000</td><td> 8515767</td></tr>\n",
       "\t<tr><td>Canada   </td><td> 35427524</td><td> 9984670</td></tr>\n",
       "\t<tr><td>Indonesia</td><td>252164800</td><td> 1904569</td></tr>\n",
       "\t<tr><td>Russia   </td><td>146000000</td><td>17125242</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & population & area\\\\\n",
       " <chr> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Australia &  23545500 &  7692024\\\\\n",
       "\t Brazil    & 202794000 &  8515767\\\\\n",
       "\t Canada    &  35427524 &  9984670\\\\\n",
       "\t Indonesia & 252164800 &  1904569\\\\\n",
       "\t Russia    & 146000000 & 17125242\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 3\n",
       "\n",
       "| name &lt;chr&gt; | population &lt;dbl&gt; | area &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Australia |  23545500 |  7692024 |\n",
       "| Brazil    | 202794000 |  8515767 |\n",
       "| Canada    |  35427524 |  9984670 |\n",
       "| Indonesia | 252164800 |  1904569 |\n",
       "| Russia    | 146000000 | 17125242 |\n",
       "\n"
      ],
      "text/plain": [
       "  name      population area    \n",
       "1 Australia  23545500   7692024\n",
       "2 Brazil    202794000   8515767\n",
       "3 Canada     35427524   9984670\n",
       "4 Indonesia 252164800   1904569\n",
       "5 Russia    146000000  17125242"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(xor(area>3e6, population>2.5e8)) %>% \n",
    "    select(name, population, area)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Rounding\n",
    "\n",
    "Show the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the [ROUND](https://sqlzoo.net/wiki/ROUND) function to show the values to two decimal places.\n",
    "\n",
    "**For South America show population in millions and GDP in billions both to 2 decimal places.**\n",
    "\n",
    "> _Millions and billions_    \n",
    "> Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 13 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>popl</th><th scope=col>gdp_b</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Argentina                       </td><td> 42.67</td><td> 477.03</td></tr>\n",
       "\t<tr><td>Bolivia                         </td><td> 10.03</td><td>  27.04</td></tr>\n",
       "\t<tr><td>Brazil                          </td><td>202.79</td><td>2254.11</td></tr>\n",
       "\t<tr><td>Chile                           </td><td> 17.77</td><td> 268.31</td></tr>\n",
       "\t<tr><td>Colombia                        </td><td> 47.66</td><td> 369.81</td></tr>\n",
       "\t<tr><td>Ecuador                         </td><td> 15.77</td><td>  87.50</td></tr>\n",
       "\t<tr><td>Guyana                          </td><td>  0.78</td><td>   2.85</td></tr>\n",
       "\t<tr><td>Paraguay                        </td><td>  6.78</td><td>  25.93</td></tr>\n",
       "\t<tr><td>Peru                            </td><td> 30.48</td><td> 204.68</td></tr>\n",
       "\t<tr><td>Saint Vincent and the Grenadines</td><td>  0.11</td><td>   0.69</td></tr>\n",
       "\t<tr><td>Suriname                        </td><td>  0.53</td><td>   5.01</td></tr>\n",
       "\t<tr><td>Uruguay                         </td><td>  3.29</td><td>  49.92</td></tr>\n",
       "\t<tr><td>Venezuela                       </td><td> 28.95</td><td> 382.42</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 13 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & popl & gdp\\_b\\\\\n",
       " <chr> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Argentina                        &  42.67 &  477.03\\\\\n",
       "\t Bolivia                          &  10.03 &   27.04\\\\\n",
       "\t Brazil                           & 202.79 & 2254.11\\\\\n",
       "\t Chile                            &  17.77 &  268.31\\\\\n",
       "\t Colombia                         &  47.66 &  369.81\\\\\n",
       "\t Ecuador                          &  15.77 &   87.50\\\\\n",
       "\t Guyana                           &   0.78 &    2.85\\\\\n",
       "\t Paraguay                         &   6.78 &   25.93\\\\\n",
       "\t Peru                             &  30.48 &  204.68\\\\\n",
       "\t Saint Vincent and the Grenadines &   0.11 &    0.69\\\\\n",
       "\t Suriname                         &   0.53 &    5.01\\\\\n",
       "\t Uruguay                          &   3.29 &   49.92\\\\\n",
       "\t Venezuela                        &  28.95 &  382.42\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 13 × 3\n",
       "\n",
       "| name &lt;chr&gt; | popl &lt;dbl&gt; | gdp_b &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Argentina                        |  42.67 |  477.03 |\n",
       "| Bolivia                          |  10.03 |   27.04 |\n",
       "| Brazil                           | 202.79 | 2254.11 |\n",
       "| Chile                            |  17.77 |  268.31 |\n",
       "| Colombia                         |  47.66 |  369.81 |\n",
       "| Ecuador                          |  15.77 |   87.50 |\n",
       "| Guyana                           |   0.78 |    2.85 |\n",
       "| Paraguay                         |   6.78 |   25.93 |\n",
       "| Peru                             |  30.48 |  204.68 |\n",
       "| Saint Vincent and the Grenadines |   0.11 |    0.69 |\n",
       "| Suriname                         |   0.53 |    5.01 |\n",
       "| Uruguay                          |   3.29 |   49.92 |\n",
       "| Venezuela                        |  28.95 |  382.42 |\n",
       "\n"
      ],
      "text/plain": [
       "   name                             popl   gdp_b  \n",
       "1  Argentina                         42.67  477.03\n",
       "2  Bolivia                           10.03   27.04\n",
       "3  Brazil                           202.79 2254.11\n",
       "4  Chile                             17.77  268.31\n",
       "5  Colombia                          47.66  369.81\n",
       "6  Ecuador                           15.77   87.50\n",
       "7  Guyana                             0.78    2.85\n",
       "8  Paraguay                           6.78   25.93\n",
       "9  Peru                              30.48  204.68\n",
       "10 Saint Vincent and the Grenadines   0.11    0.69\n",
       "11 Suriname                           0.53    5.01\n",
       "12 Uruguay                            3.29   49.92\n",
       "13 Venezuela                         28.95  382.42"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(continent=='South America') %>% \n",
    "    mutate(popl=round(population/1e6, 2), gdp_b=round(gdp/1e9, 2)) %>% \n",
    "    select(name, popl, gdp_b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Trillion dollar economies\n",
    "\n",
    "Show the `name` and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.\n",
    "\n",
    "**Show per-capita GDP for the trillion dollar countries to the nearest $1000.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 15 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>pc_gdp</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Australia     </td><td>66000</td></tr>\n",
       "\t<tr><td>Brazil        </td><td>11000</td></tr>\n",
       "\t<tr><td>Canada        </td><td>45000</td></tr>\n",
       "\t<tr><td>China         </td><td> 6000</td></tr>\n",
       "\t<tr><td>France        </td><td>40000</td></tr>\n",
       "\t<tr><td>Germany       </td><td>42000</td></tr>\n",
       "\t<tr><td>India         </td><td> 2000</td></tr>\n",
       "\t<tr><td>Italy         </td><td>33000</td></tr>\n",
       "\t<tr><td>Japan         </td><td>47000</td></tr>\n",
       "\t<tr><td>Mexico        </td><td>10000</td></tr>\n",
       "\t<tr><td>Russia        </td><td>14000</td></tr>\n",
       "\t<tr><td>South Korea   </td><td>22000</td></tr>\n",
       "\t<tr><td>Spain         </td><td>28000</td></tr>\n",
       "\t<tr><td>United Kingdom</td><td>39000</td></tr>\n",
       "\t<tr><td>United States </td><td>51000</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 15 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & pc\\_gdp\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Australia      & 66000\\\\\n",
       "\t Brazil         & 11000\\\\\n",
       "\t Canada         & 45000\\\\\n",
       "\t China          &  6000\\\\\n",
       "\t France         & 40000\\\\\n",
       "\t Germany        & 42000\\\\\n",
       "\t India          &  2000\\\\\n",
       "\t Italy          & 33000\\\\\n",
       "\t Japan          & 47000\\\\\n",
       "\t Mexico         & 10000\\\\\n",
       "\t Russia         & 14000\\\\\n",
       "\t South Korea    & 22000\\\\\n",
       "\t Spain          & 28000\\\\\n",
       "\t United Kingdom & 39000\\\\\n",
       "\t United States  & 51000\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 15 × 2\n",
       "\n",
       "| name &lt;chr&gt; | pc_gdp &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| Australia      | 66000 |\n",
       "| Brazil         | 11000 |\n",
       "| Canada         | 45000 |\n",
       "| China          |  6000 |\n",
       "| France         | 40000 |\n",
       "| Germany        | 42000 |\n",
       "| India          |  2000 |\n",
       "| Italy          | 33000 |\n",
       "| Japan          | 47000 |\n",
       "| Mexico         | 10000 |\n",
       "| Russia         | 14000 |\n",
       "| South Korea    | 22000 |\n",
       "| Spain          | 28000 |\n",
       "| United Kingdom | 39000 |\n",
       "| United States  | 51000 |\n",
       "\n"
      ],
      "text/plain": [
       "   name           pc_gdp\n",
       "1  Australia      66000 \n",
       "2  Brazil         11000 \n",
       "3  Canada         45000 \n",
       "4  China           6000 \n",
       "5  France         40000 \n",
       "6  Germany        42000 \n",
       "7  India           2000 \n",
       "8  Italy          33000 \n",
       "9  Japan          47000 \n",
       "10 Mexico         10000 \n",
       "11 Russia         14000 \n",
       "12 South Korea    22000 \n",
       "13 Spain          28000 \n",
       "14 United Kingdom 39000 \n",
       "15 United States  51000 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(gdp>=1e12) %>%\n",
    "    mutate(pc_gdp=round(gdp/(population*1000), 0)*1000) %>% \n",
    "    select(name, pc_gdp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Name and capital have the same length\n",
    "\n",
    "Greece has capital Athens.\n",
    "\n",
    "Each of the strings 'Greece', and 'Athens' has 6 characters.\n",
    "\n",
    "**Show the name and capital where the name and the capital have the same number of characters.**\n",
    "\n",
    "- You can use the [LENGTH](https://sqlzoo.net/wiki/LENGTH) function to find the number of characters in a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 26 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>capital</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Algeria </td><td>Algiers </td></tr>\n",
       "\t<tr><td>Angola  </td><td>Luanda  </td></tr>\n",
       "\t<tr><td>Armenia </td><td>Yerevan </td></tr>\n",
       "\t<tr><td>Botswana</td><td>Gaborone</td></tr>\n",
       "\t<tr><td>Canada  </td><td>Ottowa  </td></tr>\n",
       "\t<tr><td>Djibouti</td><td>Djibouti</td></tr>\n",
       "\t<tr><td>Egypt   </td><td>Cairo   </td></tr>\n",
       "\t<tr><td>Estonia </td><td>Tallinn </td></tr>\n",
       "\t<tr><td>Fiji    </td><td>Suva    </td></tr>\n",
       "\t<tr><td>Gambia  </td><td>Banjul  </td></tr>\n",
       "\t<tr><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>Peru      </td><td>Lima      </td></tr>\n",
       "\t<tr><td>Poland    </td><td>Warsaw    </td></tr>\n",
       "\t<tr><td>Russia    </td><td>Moscow    </td></tr>\n",
       "\t<tr><td>Rwanda    </td><td>Kigali    </td></tr>\n",
       "\t<tr><td>San Marino</td><td>San Marino</td></tr>\n",
       "\t<tr><td>Singapore </td><td>Singapore </td></tr>\n",
       "\t<tr><td>Taiwan    </td><td>Taipei    </td></tr>\n",
       "\t<tr><td>Togo      </td><td>Lomé      </td></tr>\n",
       "\t<tr><td>Turkey    </td><td>Ankara    </td></tr>\n",
       "\t<tr><td>Zambia    </td><td>Lusaka    </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 26 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & capital\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Algeria  & Algiers \\\\\n",
       "\t Angola   & Luanda  \\\\\n",
       "\t Armenia  & Yerevan \\\\\n",
       "\t Botswana & Gaborone\\\\\n",
       "\t Canada   & Ottowa  \\\\\n",
       "\t Djibouti & Djibouti\\\\\n",
       "\t Egypt    & Cairo   \\\\\n",
       "\t Estonia  & Tallinn \\\\\n",
       "\t Fiji     & Suva    \\\\\n",
       "\t Gambia   & Banjul  \\\\\n",
       "\t ... & ...\\\\\n",
       "\t Peru       & Lima      \\\\\n",
       "\t Poland     & Warsaw    \\\\\n",
       "\t Russia     & Moscow    \\\\\n",
       "\t Rwanda     & Kigali    \\\\\n",
       "\t San Marino & San Marino\\\\\n",
       "\t Singapore  & Singapore \\\\\n",
       "\t Taiwan     & Taipei    \\\\\n",
       "\t Togo       & Lomé      \\\\\n",
       "\t Turkey     & Ankara    \\\\\n",
       "\t Zambia     & Lusaka    \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 26 × 2\n",
       "\n",
       "| name &lt;chr&gt; | capital &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Algeria  | Algiers  |\n",
       "| Angola   | Luanda   |\n",
       "| Armenia  | Yerevan  |\n",
       "| Botswana | Gaborone |\n",
       "| Canada   | Ottowa   |\n",
       "| Djibouti | Djibouti |\n",
       "| Egypt    | Cairo    |\n",
       "| Estonia  | Tallinn  |\n",
       "| Fiji     | Suva     |\n",
       "| Gambia   | Banjul   |\n",
       "| ... | ... |\n",
       "| Peru       | Lima       |\n",
       "| Poland     | Warsaw     |\n",
       "| Russia     | Moscow     |\n",
       "| Rwanda     | Kigali     |\n",
       "| San Marino | San Marino |\n",
       "| Singapore  | Singapore  |\n",
       "| Taiwan     | Taipei     |\n",
       "| Togo       | Lomé       |\n",
       "| Turkey     | Ankara     |\n",
       "| Zambia     | Lusaka     |\n",
       "\n"
      ],
      "text/plain": [
       "    name       capital   \n",
       "1   Algeria    Algiers   \n",
       "2   Angola     Luanda    \n",
       "3   Armenia    Yerevan   \n",
       "4   Botswana   Gaborone  \n",
       "5   Canada     Ottowa    \n",
       "6   Djibouti   Djibouti  \n",
       "7   Egypt      Cairo     \n",
       "8   Estonia    Tallinn   \n",
       "9   Fiji       Suva      \n",
       "10  Gambia     Banjul    \n",
       "... ...        ...       \n",
       "17  Peru       Lima      \n",
       "18  Poland     Warsaw    \n",
       "19  Russia     Moscow    \n",
       "20  Rwanda     Kigali    \n",
       "21  San Marino San Marino\n",
       "22  Singapore  Singapore \n",
       "23  Taiwan     Taipei    \n",
       "24  Togo       Lomé      \n",
       "25  Turkey     Ankara    \n",
       "26  Zambia     Lusaka    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(nchar(name)==nchar(capital)) %>% \n",
    "    select(name, capital)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Matching name and capital\n",
    "\n",
    "The capital of Sweden is Stockholm. Both words start with the letter 'S'.\n",
    "\n",
    "**Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.**\n",
    "\n",
    "- You can use the function [LEFT](https://sqlzoo.net/wiki/LEFT) to isolate the first character.\n",
    "- You can use <> as the **NOT EQUALS** operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 24 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>capital</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Algeria  </td><td>Algiers            </td></tr>\n",
       "\t<tr><td>Andorra  </td><td>Andorra la Vella   </td></tr>\n",
       "\t<tr><td>Barbados </td><td>Bridgetown         </td></tr>\n",
       "\t<tr><td>Belize   </td><td>Belmopan           </td></tr>\n",
       "\t<tr><td>Brazil   </td><td>Brasília           </td></tr>\n",
       "\t<tr><td>Brunei   </td><td>Bandar Seri Begawan</td></tr>\n",
       "\t<tr><td>Burundi  </td><td>Bujumbura          </td></tr>\n",
       "\t<tr><td>Guatemala</td><td>Guatemala City     </td></tr>\n",
       "\t<tr><td>Guyana   </td><td>Georgetown         </td></tr>\n",
       "\t<tr><td>Kuwait   </td><td>Kuwait City        </td></tr>\n",
       "\t<tr><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>Mozambique           </td><td>Maputo                   </td></tr>\n",
       "\t<tr><td>Niger                </td><td>Niamey                   </td></tr>\n",
       "\t<tr><td>Panama               </td><td>Panama City              </td></tr>\n",
       "\t<tr><td>Papua New Guinea     </td><td>Port Moresby             </td></tr>\n",
       "\t<tr><td>Sao Tomé and Príncipe</td><td>São Tomé                 </td></tr>\n",
       "\t<tr><td>South Korea          </td><td>Seoul                    </td></tr>\n",
       "\t<tr><td>Sri Lanka            </td><td>Sri Jayawardenepura Kotte</td></tr>\n",
       "\t<tr><td>Sweden               </td><td>Stockholm                </td></tr>\n",
       "\t<tr><td>Taiwan               </td><td>Taipei                   </td></tr>\n",
       "\t<tr><td>Tunisia              </td><td>Tunis                    </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 24 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & capital\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Algeria   & Algiers            \\\\\n",
       "\t Andorra   & Andorra la Vella   \\\\\n",
       "\t Barbados  & Bridgetown         \\\\\n",
       "\t Belize    & Belmopan           \\\\\n",
       "\t Brazil    & Brasília           \\\\\n",
       "\t Brunei    & Bandar Seri Begawan\\\\\n",
       "\t Burundi   & Bujumbura          \\\\\n",
       "\t Guatemala & Guatemala City     \\\\\n",
       "\t Guyana    & Georgetown         \\\\\n",
       "\t Kuwait    & Kuwait City        \\\\\n",
       "\t ... & ...\\\\\n",
       "\t Mozambique            & Maputo                   \\\\\n",
       "\t Niger                 & Niamey                   \\\\\n",
       "\t Panama                & Panama City              \\\\\n",
       "\t Papua New Guinea      & Port Moresby             \\\\\n",
       "\t Sao Tomé and Príncipe & São Tomé                 \\\\\n",
       "\t South Korea           & Seoul                    \\\\\n",
       "\t Sri Lanka             & Sri Jayawardenepura Kotte\\\\\n",
       "\t Sweden                & Stockholm                \\\\\n",
       "\t Taiwan                & Taipei                   \\\\\n",
       "\t Tunisia               & Tunis                    \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 24 × 2\n",
       "\n",
       "| name &lt;chr&gt; | capital &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Algeria   | Algiers             |\n",
       "| Andorra   | Andorra la Vella    |\n",
       "| Barbados  | Bridgetown          |\n",
       "| Belize    | Belmopan            |\n",
       "| Brazil    | Brasília            |\n",
       "| Brunei    | Bandar Seri Begawan |\n",
       "| Burundi   | Bujumbura           |\n",
       "| Guatemala | Guatemala City      |\n",
       "| Guyana    | Georgetown          |\n",
       "| Kuwait    | Kuwait City         |\n",
       "| ... | ... |\n",
       "| Mozambique            | Maputo                    |\n",
       "| Niger                 | Niamey                    |\n",
       "| Panama                | Panama City               |\n",
       "| Papua New Guinea      | Port Moresby              |\n",
       "| Sao Tomé and Príncipe | São Tomé                  |\n",
       "| South Korea           | Seoul                     |\n",
       "| Sri Lanka             | Sri Jayawardenepura Kotte |\n",
       "| Sweden                | Stockholm                 |\n",
       "| Taiwan                | Taipei                    |\n",
       "| Tunisia               | Tunis                     |\n",
       "\n"
      ],
      "text/plain": [
       "    name                  capital                  \n",
       "1   Algeria               Algiers                  \n",
       "2   Andorra               Andorra la Vella         \n",
       "3   Barbados              Bridgetown               \n",
       "4   Belize                Belmopan                 \n",
       "5   Brazil                Brasília                 \n",
       "6   Brunei                Bandar Seri Begawan      \n",
       "7   Burundi               Bujumbura                \n",
       "8   Guatemala             Guatemala City           \n",
       "9   Guyana                Georgetown               \n",
       "10  Kuwait                Kuwait City              \n",
       "... ...                   ...                      \n",
       "15  Mozambique            Maputo                   \n",
       "16  Niger                 Niamey                   \n",
       "17  Panama                Panama City              \n",
       "18  Papua New Guinea      Port Moresby             \n",
       "19  Sao Tomé and Príncipe São Tomé                 \n",
       "20  South Korea           Seoul                    \n",
       "21  Sri Lanka             Sri Jayawardenepura Kotte\n",
       "22  Sweden                Stockholm                \n",
       "23  Taiwan                Taipei                   \n",
       "24  Tunisia               Tunis                    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_sub(name, 1, 1)==str_sub(capital, 1, 1) & \n",
    "           name != capital) %>%\n",
    "    select(name, capital)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. All the vowels\n",
    "\n",
    "**Equatorial Guinea** and **Dominican Republic** have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.\n",
    "\n",
    "**Find the country that has all the vowels and no spaces in its name.**\n",
    "\n",
    "- You can use the phrase name `NOT LIKE '%a%'` to exclude characters from your results.\n",
    "- The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Mozambique</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Mozambique\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Mozambique |\n",
       "\n"
      ],
      "text/plain": [
       "  name      \n",
       "1 Mozambique"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, 'a') & \n",
    "           str_detect(name, 'e') & \n",
    "           str_detect(name, 'i') &\n",
    "           str_detect(name, 'o') & \n",
    "           str_detect(name, 'u') & \n",
    "           str_detect(name, ' ', negate=TRUE)) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
